//
//  WDDatabase.swift
//  WDDatabase
//
//  Created by gupengling on 2021/7/14.
//

import UIKit
import FMDB
import WDLogicBehind

@objcMembers
public class WDDatabase: NSObject {
    static let DefalutTableName = "WOODY"
    private static let instance = WDDatabase()
    @discardableResult
    public static func shared() -> WDDatabase {
        return instance
    }

    var dbQueue: FMDatabaseQueue?
    var db: FMDatabase?
    
    public override init() {}

    deinit {}

    @discardableResult
    public class func createDatabaseFile() -> Bool {
        if let path = getDBFilePath(),
           !FileManager.default.fileExists(atPath: path) {
            FileManager.default.createFile(atPath: path, contents: nil, attributes: nil)
            WDLog("新建db file", .info)
            return true
        } else {
            WDLog("已经存在db file", .info)
            return false
        }
    }

    /// 删除数据库
    /// - Parameter path: 数据库路径
    public class func deleteDatabase(path: String?) {
        let p = path ?? getDBFilePath()
        if let p = p,
           FileManager.default.fileExists(atPath: p) {
            try? FileManager.default.removeItem(atPath: p)
        }
    }

    private class func getDBFilePath() -> String? {
        do {
            try WDHelper.cacheDirectory(type: .dbFile)
        } catch let error {
            WDLog(error.localizedDescription, .error)
            return nil
        }
        guard let documentUrl = FileManager.default.urls(for: WDFileType.dbFile.directory, in: .userDomainMask).first,
              let dbUrl = URL(string: documentUrl.path + "/\(WDFileType.dbFile.folderName)/") else {
            return nil
        }
        let dbFile = dbUrl.appendingPathComponent(DBName, isDirectory: true)
        return dbFile.path
    }

    private class func getDb() -> FMDatabase? {
        var db: FMDatabase?
        if let dbLocal = WDDatabase.shared().db {
            db = dbLocal
        } else if let path = WDDatabase.getDBFilePath(), !path.isEmpty {
            db = FMDatabase(path: path)
            WDDatabase.shared().db = db
        }
        return db
    }

    /// 操作数据库，内容及表等
    /// - Parameters:
    ///   - tableName: 表明
    ///   - callBack: 打开后返回 FMDatabase 对象
    public class func wdExecute(tableName: String, callBack: ((FMDatabase) -> Void)) {
        if let db = WDDatabase.getDb() {
            defer {
                db.close()
            }
            db.shouldCacheStatements = true
            db.open()
            if db.tableExists("\(tableName)") {
                callBack(db)
            }
        }
    }
    private class func wdExecuteUpdate(tableName: String? = nil, sql: String, arguments: [Any]) {
        wdExecute(tableName: (tableName ?? DefalutTableName)) { db in
            if !db.executeUpdate(sql, withArgumentsIn: arguments) {
                WDLog("Error: \(db.lastErrorMessage())", .error)
            }
        }
    }

    private class func getDbQueue() -> FMDatabaseQueue? {
        var dbQueue: FMDatabaseQueue?
        if let dbQueueLocal = WDDatabase.shared().dbQueue {
            dbQueue = dbQueueLocal
        } else if let path = WDDatabase.getDBFilePath(), !path.isEmpty {
            dbQueue = FMDatabaseQueue(path: path)
            WDDatabase.shared().dbQueue = dbQueue
        }
        return dbQueue
    }

    public class func wdExecuteQueue(tableName: String, callBack: ((FMDatabase) -> Void)) {
        if let dbQueue = WDDatabase.getDbQueue() {
            dbQueue.inDatabase { db in
                let result = db.tableExists(tableName)
                if result == false {
                    WDLog("Warning: \(db.lastErrorMessage())", .warning)
                }
                callBack(db)
            }
        }
    }
    public class func wdExecuteUpdateQueue(tableName: String? = nil, sql: String, arguments: [Any]) {
        wdExecuteQueue(tableName: (tableName ?? DefalutTableName)) { db in
            if !db.executeUpdate(sql, withArgumentsIn: arguments) {
                WDLog("Error: \(db.lastErrorMessage())", .error)
            }
        }
    }
}

extension WDDatabase {
    @discardableResult
    /// 创建表
    /// - Parameters:
    ///   - tableName: : 表名默认WOODY
    ///   - sql: sql语句 例："CREATE TABLE IF NOT EXISTS \(table) (ID TEXT PRIMARY KEY, COUNT INTEGER, TYPE INTEGER,LOGTIME DATETIME,LOGDAY TEXT, REMARK TEXT)"
    /// - Returns: 是否创建成功
    public class func createTable(tableName: String? = nil, sql: String? = nil) -> Bool {
        if let db = WDDatabase.getDb() {
            db.shouldCacheStatements = true
            if db.open() {
                let table = tableName ?? DefalutTableName
                if db.tableExists("\(table)") == false {
                    let sql_stmt = sql ?? "CREATE TABLE IF NOT EXISTS \(table) (ID TEXT PRIMARY KEY, COUNT INTEGER, TYPE INTEGER,LOGTIME DATETIME,LOGDAY TEXT, REMARK TEXT)"
                    if !db.executeStatements(sql_stmt) {
                        WDLog("Error: \(db.lastErrorMessage())", .error)
                    }
                }
                db.close()
                return true
            } else {
                WDLog("Error: \(db.lastErrorMessage())")
            }
        }
        return false
    }
    
    public class func createTableQueue(tableName: String? = nil, sql: String? = nil) {
        let table = tableName ?? DefalutTableName
        let sql_stmt = sql ?? "CREATE TABLE IF NOT EXISTS \(table) (ID TEXT PRIMARY KEY, COUNT INTEGER, TYPE INTEGER,LOGTIME DATETIME,LOGDAY TEXT, REMARK TEXT)"
        wdExecuteUpdateQueue(tableName: table, sql: sql_stmt, arguments: [])
    }

    /// 删除表
    /// - Parameter name: 表名
    public class func deleteDatabaseTable(name: String) {
        wdExecute(tableName: name) { db in
            do {
                try db.executeUpdate("DROP TABLE '\(name)'", values: nil)
            } catch let error {
                WDLog("Error: table \(name) drop failed, \(db.lastErrorMessage()), \(error.localizedDescription)", .error)
            }
        }
    }
    
    public class func deleteDatabaseTableQueue(name: String) {
        wdExecuteUpdateQueue(tableName: name, sql: "DROP TABLE '\(name)'", arguments: [])
    }

    /// 表中新增列
    /// - Parameters:
    ///   - tableName: 表名默认WOODY
    ///   - sql: sql 语句默认自带的"ALTER TABLE '\(table)' ADD '\(element)' TEXT"
    ///   - element: 要插入的列名
    public class func alterElement(tableName: String? = nil, sql: String? = nil, element: String) {
        let table = tableName ?? DefalutTableName
        wdExecute(tableName: table) { db in
            let sql = sql ?? "ALTER TABLE '\(table)' ADD ('\(element)' TEXT)"
            do {
                try db.executeUpdate(sql, values: nil)
            } catch let error {
                WDLog("Error: alter new element: \"\(element)\" to table: \"\(table)\" failed, \(db.lastErrorMessage()), \(error.localizedDescription)", .error)
            }
        }
    }

    public class func alterElementQueue(tableName: String? = nil, sql: String? = nil, element: String) {
        let table = tableName ?? DefalutTableName
        let sql = sql ?? "ALTER TABLE '\(table)' ADD ('\(element)' TEXT)"
        wdExecuteUpdateQueue(tableName: table, sql: sql, arguments: [])
    }
    /// 插入数据
    /// - Parameters:
    ///   - tableName: 表名默认WOODY
    ///   - sql: sql 语句 默认自带的 "INSERT INTO \(table) (ID,COUNT,TYPE,LOGTIME,LOGDAY,REMARK) " + "VALUES (?,?,?,?,?,?)"
    ///   - arguments: 插入数据 例：["2", 10, 0, Date(timeIntervalSince1970: 0), "LOGDAY","REMARK"]
    public class func insert(tableName: String? = nil, sql: String? = nil, arguments: [Any]) {
        let sql = sql ?? ("INSERT INTO \(tableName ?? DefalutTableName) (ID,COUNT,TYPE,LOGTIME,LOGDAY,REMARK) " + "VALUES (?,?,?,?,?,?)")
        wdExecuteUpdate(tableName: tableName, sql: sql, arguments: arguments)
    }
    public class func insertQueue(tableName: String? = nil, sql: String? = nil, arguments: [Any]) {
        let table = tableName ?? DefalutTableName
        let sql = sql ?? ("INSERT INTO \(table) (ID,COUNT,TYPE,LOGTIME,LOGDAY,REMARK) " + "VALUES (?,?,?,?,?,?)")
        wdExecuteUpdateQueue(tableName: tableName, sql: sql, arguments: arguments)
    }
    /// 更新数据
    /// - Parameters:
    ///   - tableName: 表名默认WOODY
    ///   - sql: sql 语句 例："UPDATE \(table) SET COUNT=?,TYPE=?,LOGTIME=?,LOGDAY=?,REMARK=? WHERE ID=?"
    ///   - arguments: 更新数据 例：[12, 1, Date(timeIntervalSince1970: 0), "LOGDAYupdate","REMARKupdate","1"]
    public class func update(tableName: String? = nil, sql: String? = nil, arguments: [Any]) {
        let sql = sql ?? "UPDATE \(tableName ?? DefalutTableName) SET COUNT=?,TYPE=?,LOGTIME=?,LOGDAY=?,REMARK=? WHERE ID=?"
        wdExecuteUpdate(tableName: tableName, sql: sql, arguments: arguments)
    }
    
    public class func updateQueue(tableName: String? = nil, sql: String? = nil, arguments: [Any]) {
        let table = tableName ?? DefalutTableName
        let sql = sql ?? "UPDATE \(table) SET COUNT=?,TYPE=?,LOGTIME=?,LOGDAY=?,REMARK=? WHERE ID=?"
        wdExecuteUpdateQueue(tableName: tableName, sql: sql, arguments: arguments)
    }
    /// 删除数据
    /// - Parameters:
    ///   - tableName: 表名默认WOODY
    ///   - sql: sql 语句 例："DELETE FROM \(tableName) WHERE ID = ?"
    ///   - arguments: 查找数据 例：[1]
    public class func remove(tableName: String? = nil, sql: String? = nil, arguments: [Any]) {
        let sql = sql ?? "DELETE FROM \(tableName ?? DefalutTableName) WHERE ID = ?"
        wdExecuteUpdate(tableName: tableName, sql: sql, arguments: arguments)
    }

    public class func removeQueue(tableName: String? = nil, sql: String? = nil, arguments: [Any]) {
        let table = tableName ?? DefalutTableName
        let sql = sql ?? "DELETE FROM \(table) WHERE ID = ?"
        wdExecuteUpdateQueue(tableName: tableName, sql: sql, arguments: arguments)
    }
    /// 查询数据
    /// - Parameters:
    ///   - tableName: 表名默认WOODY
    ///   - sql: sql 语句 例："SELECT * FROM \(table) WHERE ID = ?"  "SELECT * FROM '\(table)'"
    ///   - arguments: 查找数据 例：[1]
    ///   - callBack: 返回查询结果
    public class func select(tableName: String? = nil,
                             sql: String? = nil,
                             arguments: [Any],
                             callBack:((FMResultSet?, [[AnyHashable : Any]]?) -> Void)) {
        let table = tableName ?? DefalutTableName
        wdExecute(tableName: table) { db in
            let sql = sql ?? "SELECT * FROM \(table) WHERE ID = ?"
            if let rs = db.executeQuery(sql, withArgumentsIn: arguments) {
                var datas = [[AnyHashable : Any]]()
                while rs.next() {
//                    print("id = \(String(describing: rs.string(forColumn: "ID")))")
//                    print("id = \(Int(rs.int(forColumn: "COUNT")))")
//                    print("id = \(Int(rs.int(forColumn: "TYPE")))")
//                    print("id = \(String(describing: rs.string(forColumn:"REMARK")))")
//                    print("id = \(String(describing: rs.date(forColumn: "LOGTIME")))")
//                    print("id = \(String(describing: rs.string(forColumn:"LOGDAY")))")
                    if let rdic = rs.resultDictionary {
                        datas.append(rdic)
                    }
                }
                callBack(rs, datas)
                rs.close()
                return
            } else {
                WDLog("Error: \(db.lastErrorMessage())", .error)
            }
            callBack(nil, nil)
        }
    }
    
    public class func selectQueue(tableName: String? = nil,
                             sql: String? = nil,
                             arguments: [Any],
                             callBack:((FMResultSet?, [[AnyHashable : Any]]?) -> Void)) {
        let table = tableName ?? DefalutTableName
        wdExecuteQueue(tableName: table) { db in
            let sql = sql ?? "SELECT * FROM \(table) WHERE ID = ?"
            if let rs = db.executeQuery(sql, withArgumentsIn: arguments) {
                var datas = [[AnyHashable : Any]]()
                while rs.next() {
                    if let rdic = rs.resultDictionary {
                        datas.append(rdic)
                    }
                }
                callBack(rs, datas)
                rs.close()
                return
            } else {
                WDLog("Error: \(db.lastErrorMessage())", .error)
            }
            callBack(nil, nil)
        }
    }
    /// 区间选择
    /// - Parameters:
    ///   - tableName: 表名默认WOODY
    ///   - sql: sql 语句 例："SELECT * FROM '\(table)' LIMIT \(range.lowerBound), \(range.upperBound - range.lowerBound)"
    ///   - range: 区间  10...20
    ///   - callBack: 返回数据
    public class func selectPart(tableName: String? = nil,
                                 sql: String? = nil,
                                 range: CountableClosedRange<Int>,
                                 callBack:((FMResultSet?, [[AnyHashable : Any]]?) -> Void)) {
        let table = tableName ?? DefalutTableName
        wdExecute(tableName: table) { db in
            let sql = sql ?? "SELECT * FROM '\(table)' LIMIT \(range.lowerBound), \(range.upperBound - range.lowerBound)"
            do {
                let rs = try db.executeQuery(sql, values: nil)
                var datas = [[AnyHashable : Any]]()
                while rs.next() {
                    if let rdic = rs.resultDictionary {
                        datas.append(rdic)
                    }
                }
                callBack(rs, datas)
                rs.close()
                return
            } catch let error {
                WDLog("Error: select models from table \(table), \(db.lastErrorMessage()), \(error.localizedDescription)", .error)
            }
            callBack(nil, nil)
        }
    }
    
    public class func selectPartQueue(tableName: String? = nil,
                                      sql: String? = nil,
                                      range: CountableClosedRange<Int>,
                                      callBack:((FMResultSet?, [[AnyHashable : Any]]?) -> Void)) {
        let table = tableName ?? DefalutTableName
        wdExecuteQueue(tableName: table) { db in
            let sql = sql ?? "SELECT * FROM '\(table)' LIMIT \(range.lowerBound), \(range.upperBound - range.lowerBound)"
            do {
                let rs = try db.executeQuery(sql, values: nil)
                var datas = [[AnyHashable : Any]]()
                while rs.next() {
                    if let rdic = rs.resultDictionary {
                        datas.append(rdic)
                    }
                }
                callBack(rs, datas)
                rs.close()
                return
            } catch let error {
                WDLog("Error: select models from table \(table), \(db.lastErrorMessage()), \(error.localizedDescription)", .error)
            }
            callBack(nil, nil)
        }
    }
    /// 获取表的条数
    /// - Parameters:
    ///   - tableName: 表名默认WOODY
    ///   - sql: sql 语句 例："SELECT count(*) FROM '\(table)'"
    ///   - callBack: 具体数量回调
    public class func countOfTable(tableName: String? = nil, sql: String? = nil, callBack:((Int) -> Void)) {
        let table = tableName ?? DefalutTableName
        wdExecute(tableName: table) { db in
            let sql = sql ?? "SELECT count(*) FROM '\(table)'"
            do {
                let rs = try db.executeQuery(sql, values: nil)
                var count = 0
                if rs.next() {
                    count = Int(rs.int(forColumn: "count(*)"))
                }
                callBack(count)
                rs.close()
                return
            } catch let error {
                WDLog("Error: select models from table \(table), \(db.lastErrorMessage()), \(error.localizedDescription)", .error)
            }
            callBack(0)
        }
    }

    public class func countOfTableQueue(tableName: String? = nil, sql: String? = nil, callBack:((Int) -> Void)) {
        let table = tableName ?? DefalutTableName
        wdExecuteQueue(tableName: table) { db in
            let sql = sql ?? "SELECT count(*) FROM '\(table)'"
            do {
                let rs = try db.executeQuery(sql, values: nil)
                var count = 0
                if rs.next() {
                    count = Int(rs.int(forColumn: "count(*)"))
                }
                callBack(count)
                rs.close()
                return
            } catch let error {
                WDLog("Error: select models from table \(table), \(db.lastErrorMessage()), \(error.localizedDescription)", .error)
            }
            callBack(0)
        }
    }
    
    public class func transactionQueue(sqls: [String], arguments: [[Any]]) {
        if let dbQueue = WDDatabase.getDbQueue() {
            dbQueue.inTransaction { db, rollback in
                var allSuccess = false
                for (index, value) in sqls.enumerated() {
                    let obj = arguments[safe: index]
                    let result = db.executeUpdate(value, withArgumentsIn: obj ?? [])
                    if !result {
                        allSuccess = false
                        break
                    }
                }
                if !allSuccess {
                    rollback.pointee = true
                }
            }
        }
    }
}
